package com.mes.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONArray;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.mes.entity.User;
import com.yy.base.common.dao.Dao;

@Service
public class InspectionSubDataService {
	@Autowired
	private Dao dao;
	
	//加载工序信息
	public List<Map<String,Object>> getProcessList(String orgId) {
		String sql = "select ep.emes_process_id,ep.name,ep.route_no" +
			" from emes_process ep, emes_process_route epr " +
			" where epr.emes_process_route_id = ep.emes_process_route_id " +
			"  and epr.ad_org_id = ? " +
			"order by ep.route_no asc ";
		return dao.query(sql,orgId);
	}
	
	//加载责任机构
	public List<Map<String,Object>> getDutyOrgList(String orgId) {
		String sql = " select ao.ad_org_id,ao.name,atn.parent_id,ao.ad_orgtype_id,atn.seqno from ad_org ao"+
				" left join ad_treenode atn on (ao.ad_org_id=atn.node_id and atn.ad_tree_id='39118672AA004C469398D5906BD08FD4')"+
				" where 1=1"+
				" and atn.parent_id=?"+
				" and ao.isactive='Y'"+
				" order by atn.parent_id,ao.ad_orgtype_id,atn.seqno asc";
		return dao.query(sql,orgId);
	}
	
	//加载责任人列表
	public List<Map<String,Object>> getDutyManList(String orgId) {
		String sql = "select au.ad_user_id,au.name from ad_user au"+
				" where 1=1"+
				" and au.isactive='Y'"+
				" and au.ad_org_id=?";
		return dao.query(sql,orgId);
	}
	
	//加载巡检结果列表
	public List<Map<String,Object>> getResultList(String orgId) {
		String sql = "SELECT epi.emeso_process_inspection_rs_id rsId,epi.resultname FROM Emeso_PROCESS_INSPECTION_RS epi"+
				" where 1=1"+
				" and epi.ad_org_id=?";
		return dao.query(sql,orgId);
	}
	
	//获得巡检要求列表
	public List<Map<String,Object>> getInspectionList(String processId){
		String sql = "select epi.emeso_process_inspection_id inspectionId,epi.standard,epi.remark  from Emeso_PROCESS_INSPECTION epi"+
				" where 1=1"+
				" and epi.emes_process_id=? order by epi.created asc";
		return dao.query(sql,processId);
	}
	
	public List<Map<String,Object>> getInspectionRsList(String processId){
		String sql = "SELECT epi.emeso_process_inspection_rs_id rsId,epi.resultname FROM Emeso_PROCESS_INSPECTION_RS epi"+
				" where 1=1"+
				" and epi.Emes_PROCESS_ID=? order by epi.created asc";
		return dao.query(sql,processId);
	}
	
	//巡检记录历史表
	public List<Map<String,Object>> getInspectionLogList(String orgId){
		String sql = "select ep.name,epi.standard,epil.shift,epil.question,"+
				" (select t.name from ad_org t where t.ad_org_id=epil.dutyorgid) dutyorgname,"+
				" (select t.name from ad_user t where t.ad_user_id=epil.dutyuserid) dutyusername,"+
				" epil.improvement,epir.resultname,to_char(epil.created,'yyyy-MM-dd hh24:mi') subDate from emeso_process_inspection_log epil"+
				" left join emeso_process_inspection_rs epir on epil.emeso_process_inspection_rs_id=epir.emeso_process_inspection_rs_id"+
				" left join emeso_process_inspection epi on epil.emeso_process_inspection_id=epi.emeso_process_inspection_id"+
				" left join emes_process ep on epil.emes_process_id=ep.emes_process_id"+
				" where 1=1"+
				" and epil.ad_org_id=?"+
				" order by epil.created desc";
		return dao.query(sql,orgId);
	}
	
	//保存巡检记录信息
	public String saveInspectionData(String processId,String itemData,String orgId,User user){
		JSONArray jsonArr = JSONArray.fromObject(itemData);
		List<Map<String, String>> resultList = (List<Map<String, String>>) JSONArray.toCollection(jsonArr, Map.class);
		String insertSql = " insert into Emeso_PROCESS_INSPECTION_LOG("+
							" Emeso_PROCESS_INSPECT_LOG_ID,            "+
							" Emes_PROCESS_ID,                         "+
							" AD_ORG_ID,                               "+
							" AD_CLIENT_ID,                            "+
							" ISACTIVE,                                "+
							" UPDATED,                                 "+
							" UPDATEDBY,                               "+
							" CREATED,                                 "+
							" CREATEDBY,                               "+
							" Emeso_PROCESS_INSPECTION_ID,             "+
							" Emeso_PROCESS_INSPECTION_RS_ID,          "+
							" SHIFT,                                   "+
							" QUESTION,                                "+
							" DUTYORGID,                               "+
							" DUTYORG,                                 "+
							" DUTYUSERID,                              "+
							" DUTYUSER,                                "+
							" IMPROVEMENT,ISQUALIFIED                              "+
							" )                                        "+
							" VALUES(                                  "+
							" SYS_GUID(),                              "+
							" ?,                         "+
							" ?,                               "+
							" ?,                            "+
							" 'Y',                                "+
							" now(),                                 "+
							" ?,                               "+
							" now(),                                 "+
							" ?,                               "+
							" ?,             "+
							" ?,          "+
							" ?,                                   "+
							" ?,                                "+
							" ?,                               "+
							" '',                                 "+
							" ?,                              "+
							" '',                                "+
							" ?,?                              "+
							" )                                        ";
		//[{"standard":"q我去","inspectionId":"F804E2E18D724D47AF629E394E656DB5","shift":"白班","question":"wqwq",
		//"dutyOrg":"97911ABB802B461F9977B6F1C23EE693","dutyMan":"8ce79f28d73d4d56bedbe1553fbeecfc","improvement":"是",
		//"result":"BD96891D6A3E49AA90458F3D2FA35581"}]
		int result=0;
		for(Map<String, String> data:resultList){
			result +=dao.executeSql(insertSql, processId,orgId,user.getClientId(),user.getId(),user.getId(),data.get("inspectionId"),
					data.get("result"),data.get("shift"),data.get("question"),data.get("dutyOrg"),data.get("dutyMan"),data.get("improvement")
					,data.get("isQualified"));
		}
		return result > 0 ? "YES" : "NO";
	}

	//查询巡检记录列表
	public List<Map<String,Object>> searchInspectionLogList(String orgId, String processId) {
		List<Object> params = new ArrayList<Object>();
		params.add(orgId);
		String sql = "select ep.name epname,au.name,to_char(epil.created,'yyyy-MM-dd hh24:mi:ss') created,epi.standard,epil.ISQUALIFIED,epil.question,epil.shift,ao.name oname," +
				"dt_au.name dt_name,epil.IMPROVEMENT,epir.resultname from Emeso_PROCESS_INSPECTION_LOG epil " +
				"left join emeso_process_inspection epi on epil.emeso_process_inspection_id = epi.emeso_process_inspection_id " +
				"left join emeso_process_inspection_rs epir on epil.emeso_process_inspection_rs_id = epir.emeso_process_inspection_rs_id " +
				"left join emes_process ep on epil.emes_process_id = ep.emes_process_id " +
				"left join ad_user au on au.ad_user_id = epil.createdby " +
				"left join ad_org ao on ao.ad_org_id = epil.dutyorgid " +
				"left join ad_user dt_au on dt_au.ad_user_id = epil.dutyuserid " +
				"where epil.ad_org_id = ? ";
		if(StringUtils.isNotEmpty(processId) || StringUtils.isNotBlank(processId)){
			sql += "and epil.emes_process_id = ?";
			params.add(processId);
		}
		sql += " and epil.created >= trunc(sysdate-7) order by epil.created desc";
		return dao.query(sql,params.toArray());
	}
}
